Skip Headers

Oracle® OLAP Developer's Guide to the OLAP API 10g Release 2 (10.2) Part Number B14347-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 10 Retrieving Query Results

This chapter describes how to retrieve the results of a query with an Oracle OLAP API `Cursor` and how to gain access to those results. This chapter also describes how to customize the behavior of a `Cursor` to fit your method of displaying the results. For information on the class hierarchies of `Cursor` and its related classes, and for information on the `Cursor` concepts of position, fetch size, and extent, see Chapter 9, "Understanding Cursor Classes and Concepts".

This chapter includes the following topics:

- Retrieving the Results of a Query

- Navigating a CompoundCursor for Different Displays of Data

- Specifying the Behavior of a Cursor

- Calculating Extent and Starting and Ending Positions of a Value

- Specifying a Fetch Size

For the complete code of the examples in this chapter, see the example programs available from the Overview of the Oracle OLAP Java API Reference.

## Retrieving the Results of a Query

A query is an OLAP API `Source` that specifies the data that you want to retrieve from Oracle OLAP and any calculations you want Oracle OLAP to perform on that data. A `Cursor` is the object that retrieves, or fetches, the result set specified by a `Source`. Creating a `Cursor` for a `Source` involves the following steps:

1. Get a primary `Source` from an `MdmObject` or create a derived `Source` through operations on a `DataProvider` or a `Source`. For information on getting or creating `Source` objects, see Chapter 6, "Understanding Source Objects".

2. If the `Source` is a derived `Source`, prepare and commit the `Transaction` in which you created the `Source`. To prepare and commit the `Transaction`, call the `prepareCurrentTransaction` and `commitCurrentTransaction` methods of your `TransactionProvider`. For more information on preparing and committing a `Transaction`, see Chapter 8, "Using a TransactionProvider". If the `Source` is a primary `Source`, then you do not need to prepare and commit the `Transaction`.

3. Create a `CursorManagerSpecification` by calling the `createCursorManagerSpecification` method of your `DataProvider` and passing that method the `Source`.

4. Create a `SpecifiedCursorManager` by calling the `createCursorManager` method of your `DataProvider` and passing that method the `CursorManagerSpecification`. If the `Source` for the `CursorManagerSpecification` has one or more inputs, then you must also pass an array of `Source` objects that provides a `Source` for each input.

5. Create a `Cursor` by calling the `createCursor` method of the `CursorManager`. If you created the `CursorManager` with an array of input `Source` objects, then you must also pass an array of `CursorInput` objects that provides a value for each input `Source`.

Example 10-1 creates a `Cursor` for the derived `Source` named `querySource`. The example uses a `TransactionProvider` named `tp` and a `DataProvider` named `dp`. The example creates a `CursorManagerSpecification` named `cursorMngrSpec`, a `SpecifiedCursorManager` named `cursorMngr`, and a `Cursor` named `queryCursor`.

Finally, the example closes the `SpecifiedCursorManager`. When you have finished using the `Cursor`, you should close the `SpecifiedCursorManager` to free resources.

Example 10-1 Creating a Cursor

    try
    {
      tp.prepareCurrentTransaction();
    }
    catch(NotCommittableException e)
    {
      System.out.println("Cannot commit the current Transaction. " + e);
    }
    tp.commitCurrentTransaction();
    CursorManagerSpecification cursorMngrSpec =
                     dp.createCursorManagerSpecification(querySource);
    SpecifiedCursorManager cursorMngr =
                               dp.createCursorManager(cursorMngrSpec);
    Cursor queryCursor = cursorMngr.createCursor();

    // ... Use the Cursor in some way, such as to display its values.

    cursorMngr.close();

### Getting Values from a Cursor

The `Cursor` interface encapsulates the notion of a current position and has methods for moving the current position. The `ValueCursor` and `CompoundCursor` interfaces extend the `Cursor` interface. The Oracle OLAP API has implementations of the `ValueCursor` and `CompoundCursor` interfaces. Calling the `createCursor` method of a `CursorManager` returns either a `ValueCursor` or a `CompoundCursor` implementation, depending on the `Source` for which you are creating the `Cursor`.

A `ValueCursor` is returned for a `Source` that has a single set of values. A `ValueCursor` has a value at its current position, and it has methods for getting the value at the current position.

A `CompoundCursor` is created for a `Source` that has more than one set of values, which is a `Source` that has one or more outputs. Each set of values of the `Source` is represented by a child `ValueCursor` of the `CompoundCursor`. A `CompoundCursor` has methods for getting its child `Cursor` objects.

The structure of the `Source` determines the structure of the `Cursor`. A `Source` can have nested outputs, which occurs when one or more of the outputs of the `Source` is itself a `Source` with outputs. If a `Source` has a nested output, then the `CompoundCursor` for that `Source` has a child `CompoundCursor` for that nested output.

The `CompoundCursor` coordinates the positions of its child `Cursor` objects. The current position of the `CompoundCursor` specifies one set of positions of its child `Cursor` objects.

For an example of a `Source` that has only one level of output values, see Example 10-4. For an example of a `Source` that has nested output values, see Example 10-5.

An example of a `Source` that represents a single set of values is one returned by the `getSource` method of an `MdmDimension`, such as an `MdmPrimaryDimension` that represents product values. Creating a `Cursor` for that `Source` returns a `ValueCursor`. Calling the `getCurrentValue` method returns the product value at the current position of that `ValueCursor`.

Example 10-2 gets the `Source` from `mdmProdHier`, which is an `MdmPrimaryDimension` that represents product values, and creates a `Cursor` for that `Source`. The example sets the current position to the fifth element of the `ValueCursor` and gets the product value from the `Cursor`. The example then closes the `CursorManager`. In the example, `dp` is the `DataProvider`.

Example 10-2 Getting a Single Value from a ValueCursor

    Source prodSource = mdmProdHier.getSource();
    // Because prodSource is a primary Source, you do not need to
    // prepare and commit the current Transaction.
    CursorManagerSpecification cursorMngrSpec =
                   dp.createCursorManagerSpecification(productSource);
    SpecifiedCursorManager cursorMngr =
                               dp.createCursorManager(cursorMngrSpec);
    Cursor prodCursor = cursorMngr.createCursor();
    // Cast the Cursor to a ValueCursor.
    ValueCursor prodValues = (ValueCursor) prodCursor;
    // Set the position to the fifth element of the ValueCursor.
    prodValues.setPosition(5);

    // Product values are Strings. Get the String value at the current
    // position.
    String value = prodValues.getCurrentString();

    // Do something with the value, such as display it...

    // Close the SpecifiedCursorManager.
    cursorMngr.close();

Example 10-3 uses the same `Cursor` as Example 10-2. Example 10-3 uses a `do...while` loop and the `next` method of the `ValueCursor` to move through the positions of the `ValueCursor`. The `next` method begins at a valid position and returns `true` when an additional position exists in the `Cursor`. It also advances the current position to that next position.

The example sets the position to the first position of the `ValueCursor`. The example loops through the positions and uses the `getCurrentValue` method to get the value at the current position.

Example 10-3 Getting All of the Values from a ValueCursor

    // prodValues is the ValueCursor for prodSource
    prodValues.setPosition(1);
    do
    {
       System.out.println(prodValues.getCurrentValue);
    } while(prodValues.next());

The values of the result set represented by a `CompoundCursor` are in the child `ValueCursor` objects of the `CompoundCursor`. To get those values, you must get the child `ValueCursor` objects from the `CompoundCursor`.

An example of a `CompoundCursor` is one that is returned by calling the `createCursor` method of a `CursorManager` for a `Source` that represents the values of a measure as specified by selected values from the dimensions of the measure.

Example 10-4 uses a `Source`, named `units`, that results from calling the `getSource` method of an `MdmMeasure` that represents the number of units sold. The dimensions of the measure are `MdmPrimaryDimension` objects representing products, customers, times, and channels. This example uses `Source` objects that represent selected values from the default hierarchies of those dimensions. The names of those `Source` objects are `prodSel`, `custSel`, `timeSel`, and `chanSel`. The creation of the `Source` objects representing the measure and the dimension selections is not shown.

Example 10-4 joins the dimension selections to the measure, which results in a `Source` named `unitsForSelections`. It creates a `CompoundCursor`, named `unitsForSelCursor`, for `unitsForSelections`, and gets the base `ValueCursor` and the outputs from the `CompoundCursor`. Each output is a `ValueCursor`, in this case. The outputs are returned in a `List`. The order of the outputs in the `List` is the inverse of the order in which the outputs were added to the list of outputs by the successive join operations. In the example, `dp` is the `DataProvider` and `tp` is the `TransactionProvider`.

Example 10-4 Getting ValueCursor Objects from a CompoundCursor

    Source unitsForSelections = units.join(prodSel)
                                      .join(custSel)
                                      .join(timeSel)
                                      .join(chanSel);
    // Prepare and commit the current Transaction
    try
    {
      tp.prepareCurrentTransaction();
    }
    catch(NotCommittableException e)
    {
      System.out.println("Cannot commit the current Transaction. " + e );
    }
    tp.commitCurrentTransaction();

    // Create a Cursor for unitsForSelections
    CursorManagerSpecification cursorMngrSpec =
                dp.createCursorManagerSpecification(unitsForSelections);
    SpecifiedCursorManager cursorMngr =
                                 dp.createCursorManager(cursorMngrSpec);
    CompoundCursor unitsForSelCursor = (CompoundCursor)
                                        cursorMngr.createCursor();

    // Get the base ValueCursor
    ValueCursor specifiedUnitsVals = unitsForSelCursor.getValueCursor();

    // Get the outputs
    List outputs = unitsForSelCursor.getOutputs();
    ValueCursor chanSelVals = (ValueCursor) outputs.get(0);
    ValueCursor timeSelVals = (ValueCursor) outputs.get(1);
    ValueCursor custSelVals = (ValueCursor) outputs.get(2);
    ValueCursor prodSelVals = (ValueCursor) outputs.get(3);

    // You can now get the values from the ValueCursor objects.
    // When you have finished using the Cursor objects, close the
    // SpecifiedCursorManager.
    cursorMngr.close();

Example 10-5 uses the same units measure as Example 10-4, but it joins the dimension selections to the measure differently. Example 10-5 joins two of the dimension selections together. It then joins the result to the `Source` that results from joining the single dimension selections to the measure. The resulting `Source`, `unitsForSelections`, represents a query has nested outputs, which means it has more than one level of outputs.

The `CompoundCursor` that this example creates for `unitsForSelections` therefore also has nested outputs. The `CompoundCursor` has a child base `ValueCursor` and as its outputs has three child `ValueCursor` objects and one child `CompoundCursor`.

Example 10-5 joins the selection of channel dimension values, `chanSel`, to the selection of customer dimension values, `custSel`. The result is `custByChanSel`, a `Source` that has customer values as its base values and channel values as the values of its output. The example joins to `units` the selections of product and time values, and then joins `custByChanSel`. The resulting query is represented by `unitsForSelections`.

The example prepares and commits the current `Transaction` and creates a `CompoundCursor`, named `unitsForSelCursor`, for `unitsForSelections`.

The example gets the base `ValueCursor` and the outputs from the `CompoundCursor`. In the example, `dp` is the `DataProvider` and `tp` is the `TransactionProvider`.

Example 10-5 Getting Values from a CompoundCursor with Nested Outputs

    // ...in someMethod...
    Source custByChanSel = custSel.join(chanSel
    Source unitsForSelections = units.join(prodSel)
                                     .join(timeSel)
                                     .join(custByChanSel);
    // Prepare and commit the current Transaction
    try
    {
      tp.prepareCurrentTransaction();
    }
    catch(NotCommittableException e)
    {
      System.out.println("Caught exception " + e + ".");
    }
    tp.commitCurrentTransaction();

    // Create a Cursor for unitsForSelections
    CursorManagerSpecification cursorMngrSpec =
                dp.createCursorManagerSpecification(unitsForSelections);
    SpecifiedCursorManager cursorMngr =
                                 dp.createCursorManager(cursorMngrSpec);
    Cursor unitsForSelCursor = cursorMngr.createCursor();

    // Send the Cursor to a method that does different operations
    // depending on whether the Cursor is a CompoundCursor or a
    // ValueCursor.
    printCursor(unitsForSelCursor);
    cursorMngr.close();
    // ...the remaining code of someMethod...

    // The printCursor method has a do...while loop that moves through the positions
    // of the Cursor passed to it. At each position, the method prints the number of
    // the iteration through the loop and then a colon and a space. The output
    // object is a PrintWriter. The method calls the private _printTuple method and
    // then prints a new line. A "tuple" is the set of output ValueCursor values
    // specified by one position of the parent CompoundCursor. The method prints one
    // line for each position of the parent CompoundCursor.
    private void printCursor(Cursor rootCursor)
    {
      int i = 1;
      do
      {
         cpw.print(i++ + ": ");
         _printTuple(rootCursor);
         cpw.print("\n");
         cpw.flush();
      } while(rootCursor.next());
    }

    // If the Cursor passed to the _printTuple method is a ValueCursor,
    // the method prints the value at the current position of the ValueCursor.
    // If the Cursor passed in is a CompoundCursor, the method gets the
    // outputs of the CompoundCursor and iterates through the outputs,
    // recursively calling itself for each output. The method then gets the
    // base ValueCursor of the CompoundCursor and calls itself again.
    private void _printTuple(Cursor cursor)
    {
      if(cursor instanceof CompoundCursor)
      {
        CompoundCursor compoundCursor = (CompoundCursor)cursor;
        // Put an open parenthesis before the value of each output
        cpw.print("(");
        Iterator iterOutputs = compoundCursor.getOutputs().iterator();
        Cursor output = (Cursor)iterOutputs.next();
        _printTuple(output);
        while(iterOutputs.hasNext())
        {
          // Put a comma after the value of each output
          cpw.print(",");
          _printTuple((Cursor)iterOutputs.next());
        }
        // Put a comma after the value of the last output
        cpw.print(",");
        // Get the base ValueCursor
        _printTuple(compoundCursor.getValueCursor());

        // Put a close parenthesis after the base value to indicate
        // the end of the tuple.
        cpw.print(")");
      }
      else if(cursor instanceof ValueCursor)
      {
        ValueCursor valueCursor = (ValueCursor) cursor;
        if (valueCursor.hasCurrentValue())
          print(valueCursor.getCurrentValue());
        else                       // If this position has a null value
          print("NA");
      }
    }

## Navigating a CompoundCursor for Different Displays of Data

With the methods of a `CompoundCursor` you can easily move through, or navigate, its structure and get the values from its `ValueCursor` descendents. Data from a multidimensional OLAP query is often displayed in a crosstab format, or as a table or a graph.

To display the data for multiple rows and columns, you loop through the positions at different levels of the `CompoundCursor` depending on the needs of your display. For some displays, such as a table, you loop through the positions of the parent `CompoundCursor`. For other displays, such as a crosstab, you loop through the positions of the child `Cursor` objects.

To display the results of a query in a table view, in which each row contains a value from each output `ValueCursor` and from the base `ValueCursor`, you determine the position of the top-level, or root, `CompoundCursor` and then iterate through its positions. Example 10-6 displays only a portion of the result set at one time. It creates a `Cursor` for a `Source` that represents a query that is based on a measure that has unit cost values. The dimensions of the measure are the product and time dimensions. The creation of the primary `Source` objects and the derived selections of the dimensions is not shown.

The example joins the `Source` objects representing the dimension value selections to the `Source` representing the measure. It prepares and commits the current `Transaction` and then creates a `Cursor`, casting it to a `CompoundCursor`. The example sets the position of the `CompoundCursor`, iterates through twelve positions of the `CompoundCursor`, and prints out the values specified at those positions. The `TransactionProvider` is `tp` and the `DataProvider` is `dp`. The `cpw` object is a `PrintWriter`.

Example 10-6 Navigating for a Table View

    Source unitPriceByMonth = unitPrice.join(productSel)
                                       .join(timeSel);
    try
    {
      tp.prepareCurrentTransaction();
    }
    catch(NotCommittableException e)
    {
      cpw.println("Cannot prepare the current Transaction. " + e);
    }
    tp.commitCurrentTransaction();

    // Create a Cursor for unitPriceByMonth
    CursorManagerSpecification cursorMngrSpec =
                dp.createCursorManagerSpecification(unitPriceByMonth);
    SpecifiedCursorManager cursorMngr =
                                 dp.createCursorManager(cursorMngrSpec);
    (CompoundCursor) rootCursor =
                             (CompoundCursor) cursorMngr.createCursor();

    // Determine a starting position and the number of rows to display
    int start = 7;
    int numRows =12;

    cpw.println("Month     Product     Unit Price");
    cpw.println("-----     -------     ----------");

    // Iterate through the specified positions of the root CompoundCursor.
    // Assume that the Cursor contains at least (start + numRows) positions.
    for(int pos = start; pos < start + numRows; pos++)
    {
       // Set the position of the root CompoundCursor
       rootCursor.setPosition(pos);
      // Print the local values of the output and base ValueCursors.
      // The getLocalValue method gets the local value from the unique
      // value of a dimension element.
      String timeValue = ((ValueCursor)rootCursor.getOutputs().get(0))
                         .getCurrentString();
      String timeLocVal = getLocalValue(timeValue);
      String prodValue = ((ValueCursor)rootCursor.getOutputs().get(1))
                         .getCurrentString();
      String prodLocVal = getLocalValue(prodValue);
      Object price = rootCursor.getValueCursor().getCurrentValue();
      cpw.println("  " + timeLocVal + "         " + prodLocVal
                  + "        " +  price);
    };
    cursorMngr.close();

If the time selection for the query has eight values, such as the first month of each calendar quarter for the years 2001 and 2002, and the product selection has three values, then the result set of the `unitPriceByMonth` query has twenty-four positions. Example 10-6 displays the following table, which has the values specified by positions 7 through 18 of the `CompoundCursor`.

    Month     Product     Unit Price
    ------     -------     ----------
      61         13        2505.57
      61         14        3155.91
      61         15        2892.18
      64         13        2337.30
      64         14        3105.53
      64         15        2856.86
      69         13        4281.42
      69         14        6017.90
      69         15        5793.54
      72         13        4261.76
      72         14        5907.92
      72         15        5760.78

Example 10-7 uses the same query as Example 10-6. In a crosstab view, the first row is column headings, which are the values from `prodSel` in this example. The output for `prodSel` is the faster varying output because the `prodSel` dimension selection is the last output in the list of outputs that results from the operations that join the measure to the dimension selections. The remaining rows begin with a row heading. The row headings are values from the slower varying output, which is `timeSel`. The remaining positions of the rows, under the column headings, contain the `unitPrice` values specified by the set of the dimension values. To display the results of a query in a crosstab view, you iterate through the positions of the children of the top-level `CompoundCursor`.

The `TransactionProvider` is `tp` and the `DataProvider` is `dp`. The `cpw` object is a `PrintWriter`.

Example 10-7 Navigating for a Crosstab View without Pages

    Source unitPriceByMonth = unitPrice.join(productSel)
                                       .join(timeSel);
    try
    {
      tp.prepareCurrentTransaction();
    }
    catch(NotCommittableException e)
    {
      cpw.println("Cannot prepare the current Transaction. " + e);
    }
    tp.commitCurrentTransaction();

    // Create a Cursor for unitPriceByMonth
    CursorManagerSpecification cursorMngrSpec =
                dp.createCursorManagerSpecification(unitPriceByMonth);
    SpecifiedCursorManager cursorMngr =
                             dp.createCursorManager(cursorMngrSpec);
    CompoundCursor rootCursor = (CompoundCursor)
                                 cursorMngr.createCursor();

    // Get the outputs and the ValueCursor
    List outputs = rootCursor.getOutputs();
    // The first output has the values of timeSel, the slower varying output
    ValueCursor rowCursor = (ValueCursor) outputs.get(0);
    // The second output has the faster varying values of productSel
    ValueCursor columnCursor = (ValueCursor) outputs.get(1);
    // The base ValueCursor has the values from unitPrice
    ValueCursor unitPriceValues = rootCursor.getValueCursor();

    // Display the values as a crosstab.
    cpw.println("\t        Product");
    cpw.println("\t-----------------------");
    cpw.print("Month");
    do
    {
      String value = ((ValueCursor) columnCursor).getCurrentString();
      cpw.print("\t" + getLocalValue(value) + "  ");
    } while (columnCursor.next());
    cpw.println();
    cpw.println("-----\t-------\t-------\t-------");

    // Reset the column Cursor to its first element.
    columnCursor.setPosition(1);

    do
    {
      // Print the row dimension values.
      String value = ((ValueCursor) rowCursor).getCurrentString();
      cpw.print(getLocalValue(value) + "\t");
      // Loop over columns
      do
      {
        // Print data value
        cpw.print(unitPriceValues.getCurrentValue() + "\t");
      } while (columnCursor.next());

      cpw.println();

      // Reset the column Cursor to its first element.
      columnCursor.setPosition(1);
      } while (rowCursor.next());

    cursorMngr.close();

The following is a crosstab view of the values from the result set specified by the `unitPriceByMonth` query. The first line labels the rightmost three columns as having product values. The third line labels the first column as having month values and then labels each of the rightmost three columns with the product value for that column. The remaining lines have the month value in the left column and then have the data values from the units measure for the specified month and product.

    Product
           -------------------------
    Month  13       14       15
    -----  -------  -------  -------
    55     2426.07  3223.28  3042.22
    58     2412.42  3107.65  3026.12
    61     2505.57  3155.91  2892.18
    64     2337.30  3105.53  2856.86
    69     4281.42  6017.90  5793.54
    72     4261.76  5907.92  5760.78
    75     4149.12  6004.68  5730.28
    78     3843.24  5887.92  5701.76

Example 10-8 creates a `Source` that is based on a measure of units sold values. The dimensions of the measure are the customer, product, time, and channel dimensions. The `Source` objects for the dimensions represent selections of the dimension values. The creation of those `Source` objects is not shown.

The query that results from joining the dimension selections to the measure `Source` represents unit sold values as specified by the values of its outputs.

The example creates a `Cursor` for the query and then sends the `Cursor` to the `printAsCrosstab` method, which prints the values from the `Cursor` in a crosstab. That method calls other methods that print page, column, and row values.

The fastest varying output of the `Cursor` is the selection of products, which has three values (the product items 13, 14, and 15). The product values are the column headings of the crosstab. The next fastest varying output is the selection of customers, which has three values (the customers 58, 61, and 65). Those three values are the row headings. The page dimensions are selections of three time values (the months 43, 44, and 45), and one channel value (2, which is the direct sales channel).

The `TransactionProvider` is `tp` and the `DataProvider` is `dp`. The `cpw` object is a `PrintWriter`. The `getLocalValue` method gets the local value from a unique dimension value.

Example 10-8 Navigating for a Crosstab View with Pages

    // ...in someMethod...
    Source unitsForSelections = units.join(prodSel)
                                     .join(custSel)
                                     .join(timeSel)
                                     .join(chanSel);
    try
    {
      tp.prepareCurrentTransaction();
    }
    catch(NotCommittableException e)
    {
      cpw.println("Cannot prepare the current Transaction. " + e);
    }
    tp.commitCurrentTransaction();

    // Create a Cursor for unitsForSelections
    CursorManagerSpecification cursorMngrSpec =
         dp.createCursorManagerSpecification(unitsForSelections);
    SpecifiedCursorManager cursorMngr =
                                 dp.createCursorManager(cursorMngrSpec);
    CompoundCursor unitsForSelCursor = (CompoundCursor)
                                        cursorMngr.createCursor();

    // Send the Cursor to the printAsCrosstab method
    printAsCrosstab(unitsForSelCursor);

    cursorMngr.close();
    // ...the remainder of the code of someMethod...

    private void printAsCrosstab(CompoundCursor rootCursor)
    {
      List outputs = rootCursor.getOutputs();
      int nOutputs = outputs.size();

      // Set the initial positions of all outputs
      Iterator outputIter = outputs.iterator();
      while (outputIter.hasNext())
        ((Cursor) outputIter.next()).setPosition(1);

      // The last output is fastest-varying; it represents columns.
      // The next to last output represents rows.
      // All other outputs are on the page.
      Cursor colCursor = (Cursor) outputs.get(nOutputs - 1);
      Cursor rowCursor = (Cursor) outputs.get(nOutputs - 2);
      ArrayList pageCursors = new ArrayList();
      for (int i = 0 ; i < nOutputs - 2 ; i++)
      {
        pageCursors.add(outputs.get(i));
      }

      // Get the base ValueCursor, which has the data values
      ValueCursor dataCursor = rootCursor.getValueCursor();

      // Print the pages of the crosstab
      printPages(pageCursors, 0, rowCursor, colCursor, dataCursor);
    }

    // Prints the pages of a crosstab
    private void printPages(List pageCursors, int pageIndex, Cursor rowCursor,
                            Cursor colCursor, ValueCursor dataCursor)
    {
      // Get a Cursor for this page
      Cursor pageCursor = (Cursor) pageCursors.get(pageIndex);

      // Loop over the values of this page dimension
      do
      {
        // If this is the fastest-varying page dimension, print a page
        if (pageIndex == pageCursors.size() - 1)
        {
          // Print the values of the page dimensions
          printPageHeadings(pageCursors);

          // Print the column headings
          printColumnHeadings(colCursor);

          // Print the rows
          printRows(rowCursor, colCursor, dataCursor);

          // Print a couple of blank lines to delimit pages
          cpw.println();
          cpw.println();
        }

        // If this is not the fastest-varying page, recurse to the
        // next fastest varying dimension.
        else
        {
          printPages(pageCursors, pageIndex + 1, rowCursor, colCursor,
                     dataCursor);
        }
      } while (pageCursor.next());

      // Reset this page dimension Cursor to its first element.
      pageCursor.setPosition(1);
    }

    // Prints the values of the page dimensions on each page
    private void printPageHeadings(List pageCursors)
    {
      // Print the values of the page dimensions
      Iterator pageIter = pageCursors.iterator();
      while (pageIter.hasNext())
      {
        String value = ((ValueCursor) pageIter.next()).getCurrentString();
        cpw.println(getLocalValue(value));
      }
      cpw.println();
    }

    // Prints the column headings on each page
    private void printColumnHeadings(Cursor colCursor)
    {
      do
      {
         cpw.print("\t");
         String value = ((ValueCursor) colCursor).getCurrentString();
         cpw.print(getLocalValue(value));
      } while (colCursor.next());
      cpw.println();
      colCursor.setPosition(1);
    }

    // Prints the rows of each page
    private void printRows(Cursor rowCursor, Cursor colCursor,
                           ValueCursor dataCursor)
    {
      // Loop over rows
      do
      {
        // Print row dimension value
        String value = ((ValueCursor) rowCursor).getCurrentString();
        cpw.print(getLocalValue(value));
        cpw.print("\t");
        // Loop over columns
        do
        {
          // Print data value
          cpw.print(dataCursor.getCurrentValue());
          cpw.print("\t");
        } while (colCursor.next());
        cpw.println();

        // Reset the column Cursor to its first element
        colCursor.setPosition(1);
      } while (rowCursor.next());

      // Reset the row Cursor to its first element
      rowCursor.setPosition(1);
    }

Example 10-8 displays the following values, formatted as a crosstab. The display has added page, column, and row headings to identify the local values of the dimensions.

    Channel   2
    Customer 43

                 Product
            ----------------
    Month   13     14     15
    -----   --     --     --
    58       2      4      2
    61       2      1      1
    65       1      0      0

    Channel   2
    Customer 44

                 Product
            ----------------
    Month   13     14     15
    -----   --     --     --
    58       6      6      5
    61       2      2      1
    65       1      1      1

    Channel   2
    Customer 45

                 Product
            ----------------
    Month   13     14     15
    -----   --     --     --
    58       2      0      2
    61       3      2      0
    65       2      2      0

## Specifying the Behavior of a Cursor

You can specify the following aspects of the behavior of a `Cursor`.

- The fetch size of a `Cursor`, which is the number of elements of the result set that the `Cursor` retrieves during one fetch operation.

- Whether Oracle OLAP calculates the extent of the `Cursor`. The extent is the total number of positions of the `Cursor`. If the `Cursor` is a child `Cursor` of a `CompoundCursor`, its extent is relative to any slower varying outputs.

- Whether Oracle OLAP calculates the positions in the parent `Cursor` at which the value of a child `Cursor` starts or ends.

To specify the behavior of `Cursor`, you use methods of the `CursorSpecification` for that `Cursor`. To get the `CursorSpecification` for a `Cursor`, you use methods of the `CursorManagerSpecification` that you create for a `Source`.

Note: Specifying the calculation of the extent or the starting or ending position in a parent `Cursor` of the current value of a child `Cursor` can be a very expensive operation. The calculation can require considerable time and computing resources. You should only specify these calculations when your application needs them.

For more information on the relationships of `Source`, `Cursor`, `CursorSpecification`, and `CursorManagerSpecification` objects or the concepts of fetch size, extent, or `Cursor` positions, see Chapter 9.

Example 10-9 creates a `Source`, creates a `CursorManagerSpecification` for the `Source`, and then gets the `CursorSpecification` objects from a `CursorManagerSpecification`. The root `CursorSpecification` is the `CursorSpecification` for the top-level `CompoundCursor`.

Example 10-9 Getting CursorSpecification Objects from a CursorManagerSpecification

    Source unitsForSelections = units.join(prodSel)
                                     .join(custSel)
                                     .join(timeSel)
                                     .join(chanSel);
    try
    {
      tp.prepareCurrentTransaction();
    }
    catch(NotCommittableException e)
    {
      System.out.println("Caught exception " + e + ".");
    }
    tp.commitCurrentTransaction();

    // Create a Cursor for unitsForSelections
    CursorManagerSpecification cursorMngrSpec =
         dp.createCursorManagerSpecification(unitsForSelections);

    // Get the root CursorSpecification of the CursorManagerSpecification.
    CompoundCursorSpecification rootCursorSpec =
    (CompoundCursorSpecification) cursorMngrSpec.getRootCursorSpecification();

    // Get the CursorSpecification for the base values
    ValueCursorSpecification baseValueSpec =
                            rootCursorSpec.getValueCursorSpecification();

    // Get the CursorSpecification objects for the outputs
    List outputSpecs = rootCursorSpec.getOutputs();
    ValueCursorSpecification promoSelValCSpec =
                           (ValueCursorSpecification) outputSpecs.get(0);
    ValueCursorSpecification chanSelValCSpec =
                           (ValueCursorSpecification) outputSpecs.get(1);
    ValueCursorSpecification timeSelValCSpec =
                           (ValueCursorSpecification) outputSpecs.get(2);
    ValueCursorSpecification prodSelValCSpec =
                           (ValueCursorSpecification) outputSpecs.get(3);
    ValueCursorSpecification custSelValCSpec =
                           (ValueCursorSpecification) outputSpecs.get(4);

Once you have the `CursorSpecification` objects, you can use their methods to specify the behavior of the `Cursor` objects that correspond to them.

## Calculating Extent and Starting and Ending Positions of a Value

To manage the display of the result set retrieved by a `CompoundCursor`, you sometimes need to know the extent of its child `Cursor` components. You might also want to know the position at which the current value of a child `Cursor` starts in its parent `CompoundCursor`. You might want to know the span of the current value of a child `Cursor`. The span is the number of positions of the parent `Cursor` that the current value of the child `Cursor` occupies. You can calculate the span by subtracting the starting position of the value from its ending position and subtracting 1.

Before you can get the extent of a `Cursor` or get the starting or ending positions of a value in its parent `Cursor`, you must specify that you want Oracle OLAP to calculate the extent or those positions. To specify the performance of those calculations, you use methods of the `CursorSpecification` for the `Cursor`.

Example 10-10 specifies calculating the extent of a `Cursor`. The example uses the `CursorManagerSpecification` from Example 10-9.

Example 10-10 Specifying the Calculation of the Extent of a Cursor

    CompoundCursorSpecification rootCursorSpec = (CompoundCursorSpecification)
                              cursorMngrSpec.getRootCursorSpecification();
    rootCursorSpec.setExtentCalculationSpecified(true);

You can use methods of a `CursorSpecification` to determine whether the `CursorSpecification` specifies the calculation of the extent of a `Cursor` as in the following example.

    boolean isSet = rootCursorSpec.isExtentCalculationSpecified();

Example 10-11 specifies calculating the starting and ending positions of the current value of a child `Cursor` in its parent `Cursor`. The example uses the `CursorManagerSpecification` from Example 10-9.

Example 10-11 Specifying the Calculation of Starting and Ending Positions in a Parent

    CompoundCursorSpecification rootCursorSpec = (CompoundCursorSpecification)
                                cursorMngrSpec.getRootCursorSpecification();

    // Get the List of CursorSpecification objects for the outputs.
    // Iterate through the list, specifying the calculation of the extent
    // for each output CursorSpecification.
    Iterator iterOutputSpecs = rootCursorSpec.getOutputs().iterator();
    while(iterOutputSpecs.hasNext())
    {
      ValueCursorSpecification valCursorSpec = (ValueCursorSpecification)
                                                iterOutputSpecs.next();
      valCursorSpec.setParentStartCalculationSpecified(true);
      valCursorSpec.setParentEndCalculationSpecified(true);
    }

You can use methods of a `CursorSpecification` to determine whether the `CursorSpecification` specifies the calculation of the starting or ending positions of the current value of a child `Cursor` in its parent `Cursor`, as in the following example.

    boolean isSet;
    Iterator iterOutputSpecs = rootCursorSpec.getOutputs().iterator();
    ValueCursorSpecification valCursorSpec = (ValueCursorSpecification)
                                              iterOutputSpecs.next();
    while(iterOutputSpecs.hasNext())
    {
      isSet = valCursorSpec.isParentStartCalculationSpecified();
      isSet = valCursorSpec.isParentEndCalculationSpecified();
      valCursorSpec = (ValueCursorSpecification) iterOutputSpecs.next();
    }

Example 10-12 determines the span of the positions in a parent `CompoundCursor` of the current value of a child `Cursor` for two of the outputs of the `CompoundCursor`. The example uses the `unitForSelections` `Source` from Example 10-8.

The example gets the starting and ending positions of the current values of the time and product selections and then calculates the span of those values in the parent `Cursor`. The parent is the root `CompoundCursor`. The `TransactionProvider` is `tp`, the `DataProvider` is `dp`, and `cpw` is a `PrintWriter`.

Example 10-12 Calculating the Span of the Positions in the Parent of a Value

    Source unitsForSelections = units.join(prodSel)
                                     .join(custSel)
                                     .join(timeSel)
                                     .join(chanSel);
    try
    {
      tp.prepareCurrentTransaction();
    }
    catch(NotCommittableException e)
    {
      cpw.println("Caught exception " + e + ".");
    }
    tp.commitCurrentTransaction();

    // Create a CursorManagerSpecification for unitsForSelections
    CursorManagerSpecification cursorMngrSpec =
         dp.createCursorManagerSpecification(unitsForSelections);

     // Get the root CursorSpecification from the CursorManagerSpecification.
    CompoundCursorSpecification rootCursorSpec = (CompoundCursorSpecification)
                                cursorMngrSpec.getRootCursorSpecification();
    // Get the CursorSpecification objects for the outputs
    List outputSpecs = rootCursorSpec.getOutputs();
    ValueCursorSpecification timeSelValCSpec =
      (ValueCursorSpecification) outputSpecs.get(3); // output for time
    ValueCursorSpecification prodSelValCSpec =
     (ValueCursorSpecification) outputSpecs.get(1);  // output for product

    // Specify the calculation of the starting and ending positions
    timeSelValCSpec.setParentStartCalculationSpecified(true);
    timeSelValCSpec.setParentEndCalculationSpecified(true);
    prodSelValCSpec.setParentStartCalculationSpecified(true);
    prodSelValCSpec.setParentEndCalculationSpecified(true);

    // Create the CursorManager and the Cursor
    SpecifiedCursorManager cursorMngr = dp.createCursorManager(cursorMngrSpec);
    CompoundCursor rootCursor = (CompoundCursor) cursorMngr.createCursor();

    // Get the child Cursor objects
    ValueCursor baseValCursor = cursor.getValueCursor();
    List outputs = rootCursor.getOutputs();
    ValueCursor chanSelVals = (ValueCursor) outputs.get(0);
    ValueCursor timeSelVals = (ValueCursor) outputs.get(1);
    ValueCursor custSelVals = (ValueCursor) outputs.get(2);
    ValueCursor prodSelVals = (ValueCursor) outputs.get(3);

    // Set the position of the root CompoundCursor
    rootCursor.setPosition(15);

    // Get the values at the current position and determine the span
    // of the values of the time and product outputs.
    cpw.print(promoSelVals.getCurrentValue() + ", ");
    cpw.print(chanSelVals.getCurrentValue() + ", ");
    cpw.print(timeSelVals.getCurrentValue() + ", ");
    cpw.print(custSelVals.getCurrentValue() + ", ");
    cpw.print(prodSelVals.getCurrentValue() + ", ");
    cpw.println(baseValCursor.getCurrentValue());

    // Determine the span of the values of the two fastest varying outputs
    int span;
    span = (prodSelVals.getParentEnd() - prodSelVals.getParentStart()) +1);
    cpw.println("The span of " + prodSelVals.getCurrentValue() +
                " at the current position is " + span + ".")
    span = (timeSelVals.getParentEnd() - timeSelVals.getParentStart()) +1);
    cpw.println("The span of " + timeSelVals.getCurrentValue() +
                " at the current position is " + span + ".")
    cursorMngr.close();

This example displays the following text.

    CHANNEL_ROLLUP::CHANNEL::2, CALENDAR::MONTH::44,
    SHIPMENTS_ROLLUP::SHIP_TO::61, PRODUCT_ROLLUP::ITEM::15, 1.0
    The span of PRODUCT_ROLLUP::ITEM::15 at the current position is 1.
    The span of CALENDAR::MONTH::44 at the current position is 9.

## Specifying a Fetch Size

The number of elements of a `Cursor` that Oracle OLAP sends to the client application during one fetch operation depends on the fetch size specified for that `Cursor`. You can set the fetch size on the root `Cursor` for a `Source`. `Cursor` for that `CursorSpecification` to change the fetch size of the `Cursor`. The default fetch size is 100.

Example 10-13 uses the `CursorManagerSpecification` from Example 10-9. It gets the default fetch size from the root `CursorSpecification`, creates a `Cursor` and sets a different fetch size on it, and then gets the fetch size for the `Cursor`. The `TransactionProvider` is `tp`, the `DataProvider` is `dp`, and `cpw` is a `PrintWriter`.

Example 10-13 Specifying a Fetch Size

    CursorSpecification rootCursorSpec =
                            cursorMngrSpec.getRootCursorSpecification();
    context.println("The default fetch size is "
                    + rootCursorSpec.getDefaultFetchSize() + ".");
    CursorManager cursorMngr = dp.createCursorManager(cursorMngrSpec);
    Cursor rootCursor = cursorMngr.createCursor();
    rootCursor.setFetchSize(10);
    context.println("The fetch size is now " + rootCursor.getFetchSize())
                     + ".";

This example displays the following text.

    The default fetch size is 100.
    The fetch size is now 10.